﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using CMP.ERP.Helper;
using CMP.ERP.Model;

namespace CMP.ERP.DAL
{
    public class MateCompanyDAL
    {
        SQLHelper b = new SQLHelper();
        /// <summary>
        /// 根据快递公司编码查询匹配地址(省/市)
        /// </summary>
        /// <returns></returns>
        public DataTable ShowExpressMatch(int Express_Company_Id)
        {
            string sql = "select a.ExpressMatchId,b.Express_Company_Id,b.Express_Company_Name,c.P_Id,c.P_Name,c.P_City from ExpressMatch a inner join ExpressCompany b on a.Express_Company_Id=b.Express_Company_Id inner join Province c on a.P_Id=c.P_Id where b.Express_Company_Id=" + Express_Company_Id + " and c.P_City='0'";
            return b.ExecDataTable(sql);
        }
        /// <summary>
        /// 根据快递公司编码查询匹配地址(市/区)
        /// </summary>
        /// <param name="Express_Company_Id"></param>
        /// <returns></returns>
        public DataTable GetExpressMatchShow(int Express_Company_Id)
        {
            string sql = "select a.ExpressMatchId,b.Express_Company_Id,b.Express_Company_Name,c.P_Id,c.P_Name,c.P_City from ExpressMatch a inner join ExpressCompany b on a.Express_Company_Id=b.Express_Company_Id inner join Province c on a.P_Id=c.P_Id where b.Express_Company_Id=" + Express_Company_Id + " and c.P_City!='0'";
            return b.ExecDataTable(sql);
        }
        /// <summary>
        /// 显示地区信息
        /// </summary>
        /// <returns></returns>
        public DataTable ShowProvince()
        {
            string sql = "select P_Id,P_Name,P_City from Province";
            return b.ExecDataTable(sql);
        }
        /// <summary>
        /// 判断是否有上级城市
        /// </summary>
        /// <param name="P_City"></param>
        /// <returns></returns>
        public DataTable ShowProvince1(string P_City)
        {
            string sql = "select P_Id,P_Name,P_City from Province where P_City=" + P_City + "";
            return b.ExecDataTable(sql);
        }
        /// <summary>
        /// 根据快递公司编码查询匹配地址(所有)
        /// </summary>
        /// <param name="Express_Company_Id"></param>
        /// <returns></returns>
        public DataTable GetExpressMatch1(int Express_Company_Id)
        {
            string sql = "select a.ExpressMatchId,a.Express_Company_Id,a.P_Id,c.P_Name from ExpressMatch a join ExpressCompany b on a.Express_Company_Id=b.Express_Company_Id join Province c on a.P_Id=c.P_Id  where b.Express_Company_Id=" + Express_Company_Id + "";
            return b.ExecDataTable(sql);
        }
        /// <summary>
        /// 根据快递公司名称查询快递匹配信息
        /// </summary>
        /// <param name="Express_Company_Id">快递公司编码</param>
        /// <returns></returns>
        public DataTable SelExpressMatch(int? Express_Company_Id)
        {
            string sql = string.Format("select a.ExpressMatchId,b.Express_Company_Id,b.Express_Company_Name,c.P_Id,c.P_Name,c.P_City from ExpressMatch a inner join ExpressCompany b on a.Express_Company_Id=b.Express_Company_Id inner join Province c on a.P_Id=c.P_Id where b.Express_Company_Id like '%{0}%'", Express_Company_Id);
            return b.ExecDataTable(sql);
        }
        /// <summary>
        /// 添加匹配信息
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int AddExpressMatch(int Express_Company_Id, int P_Id)
        {
            string sql = string.Format("insert into ExpressMatch(Express_Company_Id,P_Id) values('{0}','{1}')", Express_Company_Id, P_Id);
            return b.ExecNonquery(sql);
        }
        /// <summary>
        /// 删除匹配信息
        /// </summary>
        /// <param name="MateCompany_Id"></param>
        /// <returns></returns>
        public int DelExpressMatch(int Express_Company_Id)
        {
            string sql = string.Format("delete from ExpressMatch where Express_Company_Id='{0}'", Express_Company_Id);
            return b.ExecNonquery(sql);
        }
    }
}
